package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;

import model.City;
import model.Country;
import model.Outsider;

import util.Number;

public class DBSupplier extends DBOutsider implements IFSupplier
{
	private Connection con;

	public DBSupplier()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public Outsider searchSupplierID(int outsiderID) throws SQLException
	{
		Outsider newOutsider = new Outsider();

		String query = "SELECT o.*, c.city_name, cn.country_name, cn.country_tld FROM outsider AS o LEFT JOIN city AS c ON o.outsider_city_id = c.city_id LEFT JOIN country AS cn ON c.city_country_id = cn.country_id WHERE o.outsider_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchOutsiderID", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, outsiderID);

			ResultSet results = ps.executeQuery();

			while(results.next())
			{
				newOutsider = buildOutsiderObject(results, true);
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchOutsiderID - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return newOutsider;
	}

	@Override
	public Outsider searchSupplierByName(String name) throws SQLException
	{
		Outsider newOutsider = new Outsider();

		String query = "SELECT o.*, c.city_name, cn.country_name FROM outsider AS o LEFT JOIN city AS c ON o.outsider_city_id = c.city_id LEFT JOIN country AS cn ON c.city_country_id = cn.country_id WHERE o.outsider_name = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchOutsiderByName", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, name);

			ResultSet results = ps.executeQuery();

			while(results.next())
			{
				newOutsider = buildOutsiderObject(results, false);
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchOutsiderByName - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return newOutsider;
	}

	@Override
	public ArrayList<Outsider> printAllSuppliers(int itemID) throws SQLException
	{
		ArrayList<Outsider> outsiderList = new ArrayList<Outsider>();

		//String query = "SELECT o.outsider_id, o.outsider_name, cn.country_tld FROM outsider AS o LEFT JOIN city AS c ON o.outsider_city_id = c.city_id LEFT JOIN country AS cn ON c.city_country_id = cn.country_id WHERE o.outsider_job_id IS NULL" + (exlucdingOutsiders.length() > 0 ? " AND o.outsider_id NOT IN (" + exlucdingOutsiders.substring(0, exlucdingOutsiders.length()-1) + ")" : "" ) + " ORDER BY o.outsider_name ASC";
		String query = "SELECT o.outsider_id, o.outsider_name, cn.country_tld FROM outsider AS o LEFT JOIN city AS c ON o.outsider_city_id = c.city_id LEFT JOIN country AS cn ON c.city_country_id = cn.country_id WHERE o.outsider_job_id IS NULL " + (itemID > 0 ? " AND outsider_id NOT IN (SELECT is_outsider_id FROM item_supplier WHERE is_item_id = ?)" : "");

		if (DBConnection.doDebug())
			DBConnection.printDebug("printAllOutsiders", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			if (itemID > 0)
				ps.setInt(1, itemID);

			ResultSet results = ps.executeQuery();

			while(results.next())
			{
				Outsider newOutsider = new Outsider();

				newOutsider.setOutsiderID(results.getInt("outsider_id"));
				newOutsider.setName(results.getString("outsider_name"));
				newOutsider.setCity(new City(new Country("", results.getString("country_tld"))));

				outsiderList.add(newOutsider);
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - printAllOutsiders - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return outsiderList;
	}

	@Override
	public ArrayList<Outsider> searchSupplierEverything(boolean active, String search) throws SQLException
	{
		boolean number = Number.isNumber(search);

		return searchOutsiders(active, search, "(o.outsider_name LIKE ? OR o.outsider_phoneno LIKE ? OR o.outsider_email LIKE ? OR c.city_name LIKE ? OR cn.country_name LIKE ? " + (number ? " OR o.outsider_id = ?" : "") + " )", (number ? 6 : 5));
	}

	@Override
	public ArrayList<Outsider> searchSupplierName(boolean active, String search)
	{
		return searchOutsiders(active, search, "o.outsider_name LIKE ?", 1);
	}

	@Override
	public ArrayList<Outsider> searchSupplierEmail(boolean active, String search)
	{
		return searchOutsiders(active, search, "o.outsider_email LIKE ?", 1);
	}

	@Override
	public ArrayList<Outsider> searchSupplierPhoneNo(boolean active, String search)
	{
		return searchOutsiders(active, search, "o.outsider_phoneno LIKE ?", 1);
	}

	@Override
	public ArrayList<Outsider> searchSupplierCity(boolean active, String search)
	{
		return searchOutsiders(active, search, "c.city_name LIKE ?", 1);
	}

	@Override
	public ArrayList<Outsider> searchSupplierCountry(boolean active, String search)
	{
		return searchOutsiders(active, search, "cn.country_name LIKE ?", 1);
	}

	/**
	 * Our method for handling outsider search
	 * 
	 * @param 	boolean		are we searching for active only or not
	 * @param	String 		our search word, we use it for the prepared statement
	 * @param 	String		our extra query (in order to keep everything in one method and not 500 lines of code..)
	 * @param 	int			might be kinda hardcoded, number of prepared statements we have to do, most (only) used in searchOutsiderEverything
	 * 
	 * @return  ArrayList<Outsider>
	 */
	private ArrayList<Outsider> searchOutsiders(boolean active, String search, String clause, int num)
	{
		ArrayList<Outsider> outsiderList = new ArrayList<Outsider>();

		String query = "SELECT o.*, c.city_name, cn.country_name, cn.country_tld FROM outsider AS o LEFT JOIN city AS c ON o.outsider_city_id = c.city_id LEFT JOIN country AS cn ON c.city_country_id = cn.country_id WHERE o.outsider_job_id IS NULL" + (active ? " AND o.outsider_active = '1'" : "") + ((clause.length() > 0) ? (" AND " + clause) : "") + " ORDER BY o.outsider_name ASC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchOutsiders", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			if (clause.length() > 0)
			{
				ps.setString(1, "%" + search + "%");

				if (num > 1)
				{
					for(int i = 2; i <= num; i++)
						ps.setString(i, (i == 6 ? search : "%" + search + "%"));						
				}
			}

			ResultSet results = ps.executeQuery();

			while(results.next())
			{
				Outsider newOutsider = buildOutsiderObject(results, true);

				outsiderList.add(newOutsider);
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchOutsiders - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return outsiderList;
	}

	private Outsider buildOutsiderObject(ResultSet results, boolean flag) throws SQLException
	{
		Outsider newOutsider = new Outsider();

		newOutsider.setOutsiderID(results.getInt("outsider_id"));
		newOutsider.setName(results.getString("outsider_name"));
		newOutsider.setActive(results.getBoolean("outsider_active"));
		newOutsider.setEmail(results.getString("outsider_email"));
		newOutsider.setPhoneNo(results.getString("outsider_phoneno"));
		newOutsider.setCity(new City(results.getInt("outsider_city_id"), results.getString("city_name"), (flag ? (new Country(results.getString("country_name"), results.getString("country_tld"))) : (new Country(results.getString("country_name"))))));

		return newOutsider;
	}
}